﻿///#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

using System.IO;
using System.Text;

using System.Net;
using System.Net.Sockets;
using System.Threading;
using System.Net.Mail;

namespace ukeasbjWeb
{
    public partial class ukeas_bonus_US_T_110315 : Bonus
    {
        //从配置文件Web.Config 中读出数据库设置
        ///string strCnn = ConfigurationManager.AppSettings["glCnnStr"]; 

        /// <summary>
        /// 顾问、文案控件的初始化
        /// </summary>
        private void Init_CounsellorWriter()
        {

            string sDDList_Office = Request["Office"];
            sDDList_Office = Common_SXD.get_C_Office(sDDList_Office);
            // 载入顾问、文案数据，绑定到 DDList_Counsellor_Writer 中
            string sSql = "select * from Employees ";
            sSql += "where C_N_Employee like '?' or ( ( Is_Counsellor = 1 or  Is_Writer = 1 ) and Is_Active = 1 and Office ='" + sDDList_Office + "' ) ";
            sSql += "order by Code_Employee asc";
            Common_SXD.Init_DropDownList(DDList_Counsellor_Writer, "Employees", sSql);
        }

        /// <summary>
        /// 地区、计奖年、月的初始化
        /// </summary>
        private void Init_Office_YearMonth()
        {
            TextBox_Year_Month.Text = Common_SXD.TimeToYM(DateTime.Now.ToString());
        }

        decimal J9_Jiang, J9_Kou, J10, J11_Jiang, J11_Kou, J12, J13;
        int Max_TopPart = 0, Max_MiddlePart = 0, Max_BottomPart = 0;

        /// <summary>
        ///  初始化：计算奖金表（美国文案）
        /// </summary>
        private void Init_Total_Bonus(string sTime)
        {
            ////////////////////////初始值为空
            Label_b9.Text = "";
            Label_b10.Text = "";

            Label_b11.Text = "";
            Label_b12.Text = "";

            Label_b13.Text = "";

            Label_Total_Bonus.Text = "";

            Label_Total_J9.Text = "";
            Label_Total_J11.Text = "";
            ///////////////////////			

            System.DateTime startDateTime20090925 =
                new System.DateTime(2009,        // Year
                9,            // Month
                25,            // Day
                0,            // Hour
                0,            // Minute
                0,            // Second
                0             // Millisecond
                );

            System.DateTime flagStartDateTime = Convert.ToDateTime(Common_SXD.strStartDate);

            decimal b9 = 0, b10 = 0, b11 = 0, b12 = 0, b13 = 0;

            b9 = Math.Round((J9_Jiang - J9_Kou) * 0.01m, 2);

            b10 = Math.Round(J10 * 0.01m, 2);

            b11 = Math.Round((J11_Jiang - J11_Kou) * 0.005m, 2);

            b12 = Math.Round(J12 * 0.01m, 2);

            b13 = Math.Round(J13 * 0.01m, 2);

            Label_b9.Text = b9.ToString().Trim();
            Label_b10.Text = b10.ToString().Trim();

            Label_b11.Text = b11.ToString().Trim();
            Label_b12.Text = b12.ToString().Trim();

            Label_b13.Text = b13.ToString().Trim();

            decimal total = b9 + b10 + b11 + b12 + b13;
            total = Math.Round(total, 2);
            Label_Total_Bonus.Text = total.ToString().Trim();
            Label_Total_J9.Text = (J9_Jiang - J9_Kou).ToString().Trim();
            Label_Total_J11.Text = (J11_Jiang - J11_Kou).ToString().Trim();
            
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
            Common_SXD.Is_LongIn(Response, Session, Request);

            //////只允许顾问、文案、OM、总部会计、Admin 访问
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_counsellor"]);
            bool bIs_Writer = Convert.ToBoolean(Session["Is_Writer"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_G_M = Convert.ToBoolean(Session["Is_G_M"]);
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            if (!bIs_Counsellor && !bIs_Writer && !bIs_O_M && !bIs_Admin 
                && !bIs_Accountant && !bIs_G_M)
            {
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，您不能使用本功能。" +
                    "<br>请用其它身份登录。");
                Response.Write("</fong></h2>");
                Response.End();

            }

            if (!IsPostBack)
            {	// 初始化页面

                //暂时关闭
                Bt_Bonus_Exel.Enabled = false;

                //////顾问、文案，设定姓名，关闭选项
                DDList_Counsellor_Writer.Enabled = true;
                DDList_Counsellor_Writer.Visible = true;
                if ((bIs_Counsellor || bIs_Writer))
                    if (!bIs_O_M)
                        if (!bIs_Admin)
                            if (!bIs_Accountant)
                            {
                                DDList_Counsellor_Writer.Enabled = false;
                                DDList_Counsellor_Writer.Visible = false;
                            }

                Init_Office_YearMonth();
                Init_CounsellorWriter();

            }
        }

        protected void Bt_Refress_Search_Click(object sender, EventArgs e)
        {
            // 填写奖金计算网页
            Common_SXD.Set_BonusDate(TextBox_Year_Month.Text, false);
            Init_Tables_Bonus9_Jiang();
            Init_Tables_Bonus9_Kou();
            Init_Tables_Bonus10();

            Init_Tables_Bonus11_Jiang();
            Init_Tables_Bonus11_Kou();
            Init_Tables_Bonus12();

            Init_Tables_Bonus13();

            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year;
            Init_Total_Bonus(TextBox_Year_Month.Text);

            string strOffice = Request["Office"].ToString().Trim();
            strOffice = Common_SXD.get_C_Office(strOffice);

            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            System.DateTime bonusDateTime20101201 =
    new System.DateTime(2010,        // Year
    12,            // Month
    1,            // Day
    0,            // Hour
    0,            // Minute
    0,            // Second
    0             // Millisecond
    );

            string temp = TextBox_Year_Month.Text + "-1";
            System.DateTime bonusNow = Convert.ToDateTime(temp);
            /// excel 奖金表的行数限制，分为上中下3个最大行数。
            if (bonusNow >= bonusDateTime20101201 && Max_TopPart <= 16 && Max_MiddlePart <= 16
                && Max_BottomPart <= 16)
            {
                Bt_Bonus_Exel.Enabled = true; //允许发送 excel格式的(101217版)奖金表

                //               SynchronousSocketClient MyClient = new SynchronousSocketClient();
                // 生成 exel 奖金文件
                //               MyClient.StartClient(strOffice, strCounsellor_Writer, Common_SXD.tempYear, Common_SXD.tempMonth, Response);
            }
            else
            {
                Bt_Bonus_Exel.Enabled = false;
                Response.Write("无法生成 excel 格式的(110315版)美国文案奖金表（日期不合适或者数据超出表格范围）！");

            }

        }

        protected void Bt_Bonus_Exel_Click(object sender, EventArgs e)
        {
            Response.Write("调试程序还在调试，无法生成 excel 格式的(110315版)美国文案奖金表！");

        }

        /// <summary>
        ///  初始化奖金10，美国(文书)文案结案奖 
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 美国'全程一次'， '全程二次', ，'申请学校'。
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus10()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus10.Rows.Clear();
            SubTotal_Lines J10_Return_Filling_Table = new SubTotal_Lines();
            
            //生成 sql 命令串
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "", strFilter;
#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#endif            
            //sSql += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// A) 美国(文书)文案结案奖，美国'全程一次'， '全程二次'，'申请学校'，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract ) AND";
            //strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";

            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR Student_Type like '申请学校' ) AND Writer = '" + strCounsellor_Writer + "'";
            
            J10_Return_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J10_Return_Filling_Table, 1, Table_Bonus10);

            J10 = J10_Return_Filling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J10.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus10.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }


        /// <summary>
        ///  初始化奖金13，美国全程(签证后续)文案结案奖 
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 美国'全程一次'， '全程二次'。
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus13()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus13.Rows.Clear();
            SubTotal_Lines J13_Return_Filling_Table = new SubTotal_Lines();
            
            //生成 sql 命令串
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "", strFilter;
#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#elif MySql

            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
            
#endif
            
            
            //sSql += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// A) 美国、加拿大 全程(签证后续)文案结案奖，美国'全程一次'， '全程二次'，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract ) AND";
            //strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";

            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' ) AND After_Visa_US = '" + strCounsellor_Writer + "'";

            J13_Return_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J13_Return_Filling_Table, 1, Table_Bonus13);

            J13 = J13_Return_Filling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J13.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus13.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ////////////////////////////////////////////////////////////////////////////////
            if (MyLines > Max_BottomPart)
                Max_BottomPart = MyLines;

        }



        /// <summary>
        ///  初始化奖金9，美国(文书)文案完成Offer奖，发奖计算。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 美国'全程一次'， '全程二次', ，'申请学校'。
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus9_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus9_Jiang.Rows.Clear();
            SubTotal_Lines J9_JiangReturn_Filling_Table = new SubTotal_Lines();
            
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";
            
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";


#endif
            
            string strFilter = "";
            /// <newpara>
            /// A) 美国、加拿大 '全程一次'， '全程二次', ，'申请学校'。
            /// </newpara>
            strFilter += "((Date_Completion_Offer_US >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer_US <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR Student_Type like '申请学校' )  AND Writer = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J9_JiangReturn_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J9_JiangReturn_Filling_Table, 1, Table_Bonus9_Jiang);
            
            J9_Jiang = J9_JiangReturn_Filling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J9_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus9_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金9，美国(文书)文案完成Offer奖，扣奖计算。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A)美国 '全程一次'， '全程二次', ，'申请学校' 
        ///    2011年7月26日之前
        /// </newpara>
        /// AA)美国 '全程一次'， '全程二次', ，'申请学校' 
        ///    2011年7月26日之后
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        ///    2011年7月26日之前
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus9_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus9_Kou.Rows.Clear();
            SubTotal_Lines J9_KouReturn_Filling_Table = new SubTotal_Lines();
            
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            
            
#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#endif
            string strFilter = "";
            /// <newpara>
            /// A) 美国、加拿大 '全程一次'， '全程二次', ，'申请学校' 
            ///    2011年7月26日之前
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer_US > '1930-1-1' AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR  Student_Type like '申请学校' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J9_KouReturn_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J9_KouReturn_Filling_Table, 1, Table_Bonus9_Kou);

            //string strOffice = Request["Office"].ToString().Trim();
            
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";
            
            
#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

#endif
            strFilter = "";
            /// <newpara>
            /// AA) 美国 '全程一次'， '全程二次', ，'申请学校' 
            ///    2011年7月26日之后
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer_US > '1930-1-1' AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR  Student_Type like '申请学校' )  AND Writer = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J9_KouReturn_Filling_Table = Filling_Table(sSql + strFilter, J9_KouReturn_Filling_Table, 1, Table_Bonus9_Kou);

            J9_Kou = J9_KouReturn_Filling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J9_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus9_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////

            ////////////////////////////////////////////////////////////////////////////////
            if (MyLines > Max_TopPart)
                Max_TopPart = MyLines;

        }

        /// <summary>
        ///  初始化奖金12，美国(申请)文案结案奖 
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 美国'全程一次'， '全程二次', ，'申请学校'。
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus12()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus12.Rows.Clear();
            SubTotal_Lines J12_Return_Filling_Table = new SubTotal_Lines();
            
            //生成 sql 命令串
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "", strFilter;

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";

#elif MySql

            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment - Termination_Refund as 'Shishoujinge' from contract where ";
#endif            
            //sSql += "Office like '" + strOffice + "' AND";
            /// <newpara>
            /// A) 美国(文书)文案结案奖，美国'全程一次'， '全程二次'，'申请学校'，净收入（一期款 +  二期款 - 退款） 为结案的金额
            /// </newpara>
            /// 
            strFilter = "";
            strFilter += "(Date_first_payment >= '1930-1-1') AND (Date_first_payment <= Date_Completion_Contract ) AND";
            //strFilter += " AND (Date_second_payment >= '1930-1-1') AND (Date_second_payment <= Date_Completion_Contract ) AND";

            strFilter += "((Date_Completion_Contract >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Contract <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR Student_Type like '申请学校' ) AND Net_Applicant_US = '" + strCounsellor_Writer + "'";

            J12_Return_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J12_Return_Filling_Table, 1, Table_Bonus12);

            J12 = J12_Return_Filling_Table.Sub_Total;
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J12.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus12.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ////////////////////////////////////////////////////////////////////////////////
            if (MyLines > Max_MiddlePart)
                Max_MiddlePart = MyLines;

        }


        /// <summary>
        ///  初始化奖金9，美国(申请)文案完成Offer奖，发奖计算。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A) 美国'全程一次'， '全程二次', ，'申请学校'。
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus11_Jiang()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus11_Jiang.Rows.Clear();
            SubTotal_Lines J11_JiangReturn_Filling_Table = new SubTotal_Lines();
            
            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";

            

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";
#elif MySql
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, First_Payment + Second_Payment as 'Shishoujinge' from contract where ";
#endif
            string strFilter = "";
            /// <newpara>
            /// A) 美国、加拿大 '全程一次'， '全程二次', ，'申请学校'。
            /// </newpara>
            strFilter += "((Date_Completion_Offer_US >= '" + Common_SXD.strStartDate + "') AND (Date_Completion_Offer_US <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR Student_Type like '申请学校' )  AND Net_Applicant_US = '" + strCounsellor_Writer + "'";

            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J11_JiangReturn_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J11_JiangReturn_Filling_Table, 1, Table_Bonus11_Jiang);
            J11_Jiang = J11_JiangReturn_Filling_Table.Sub_Total;
            
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J11_Jiang.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus11_Jiang.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ///////////////////////////////
            if (MyLines > Max_MiddlePart)
                Max_MiddlePart = MyLines;

        }

        /// <summary>
        ///  初始化奖金9，美国(申请)文案完成Offer奖，扣奖计算。
        /// </summary>
        /// <remarks>
        /// <newpara>
        /// A)美国 '全程一次'， '全程二次', ，'申请学校' 
        ///    2011年7月26日之前
        /// </newpara>
        /// AA)美国 '全程一次'， '全程二次', ，'申请学校' 
        ///    2011年7月26日之后
        /// </newpara>
        /// <newpara>
        /// B) 含美国'%联办'。
        ///    2011年7月26日之前
        /// </newpara>
        /// </remarks>
        private void Init_Tables_Bonus11_Kou()
        {
            int MyLines = 0;
            //清除表中的内容
            Table_Bonus11_Kou.Rows.Clear();
            SubTotal_Lines J11_KouReturn_Filling_Table = new SubTotal_Lines();

            //string strOffice = Request["Office"].ToString().Trim();
            string strCounsellor_Writer;
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            string sSql = "";
            

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#elif MySql

            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#endif
            string strFilter = "";
            /// <newpara>
            /// A) 美国、加拿大 '全程一次'， '全程二次', ，'申请学校' 
            ///    2011年7月26日之前
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer_US > '1930-1-1' AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR  Student_Type like '申请学校' )  AND Net_Applicant_US = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund < '2011-7-26' AND Date_Finished_Termination_Refund < '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J11_KouReturn_Filling_Table = Filling_Table_with_Heads(sSql + strFilter, J11_KouReturn_Filling_Table, 1, Table_Bonus11_Kou);

            //string strOffice = Request["Office"].ToString().Trim();
            if (DDList_Counsellor_Writer.Visible)
            {
                strCounsellor_Writer = DDList_Counsellor_Writer.SelectedItem.Text;
            }
            else
                strCounsellor_Writer = Session["C_N_Employee"].ToString().Trim();

            sSql = "";
            

#if MSSql
            sSql = "select  Student_Name, Destinations_OS+':'+Student_Type as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";

#elif MySql
            
            sSql = "select  Student_Name, CONCAT(Destinations_OS, ':', Student_Type) as Student_Type, Termination_Refund as 'Shituijinge' from contract where ";
#endif            
            strFilter = "";
            /// <newpara>
            /// AA) 美国 '全程一次'， '全程二次', ，'申请学校' 
            ///    2011年7月26日之后
            /// </newpara>
            //strFilter += "Office like '" + strOffice + "' AND";
            strFilter += "((Date_Finished_Termination_Refund >= '" + Common_SXD.strStartDate + "') AND (Date_Finished_Termination_Refund <= '" + Common_SXD.strEndingDate;
            strFilter += "')) AND Date_Completion_Offer_US > '1930-1-1' AND ( Destinations_OS like '美国' OR  Destinations_OS like '加拿大' ) AND ( Student_Type like '全程%' OR  Student_Type like '申请学校' )  AND Net_Applicant_US = '";
            strFilter += strCounsellor_Writer + "'AND Termination_Refund > 0 ";
            strFilter += "AND ( Date_Termination_Refund >= '2011-7-26' OR Date_Finished_Termination_Refund >= '2011-7-26') ";
            //			sSql += " order by " + Sort_Search_Contract( sSearchKey, sOffice, sStart_Date, sEnd_Date, sSearchValue);

            J11_KouReturn_Filling_Table = Filling_Table(sSql + strFilter, J11_KouReturn_Filling_Table, 1, Table_Bonus11_Kou);
            J11_Kou = J11_KouReturn_Filling_Table.Sub_Total;
            
            ////////////////生成小计
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.Controls.Add(new LiteralControl(" "));
                row.Cells.Add(cell);

                HtmlTableCell cell2 = new HtmlTableCell();
                cell2.Controls.Add(new LiteralControl("合计:"));
                row.Cells.Add(cell2);

                HtmlTableCell cell3 = new HtmlTableCell();
                cell3.Controls.Add(new LiteralControl(J11_Kou.ToString().Trim()));
                row.Cells.Add(cell3);

                Table_Bonus11_Kou.Rows.Add(row);
                //			row.BgColor="Tan";
            }
            ////////////////////////////////////////////////////////////////////////////////
            if (MyLines > Max_MiddlePart)
                Max_MiddlePart = MyLines;

        }


    }
}
